from flask import Flask, request, render_template, g, make_response
from sqlite3 import connect
from json import loads, dumps
import os, re
import pprint
from sched_db.querybuilder import *

DB_FILE = "./201201.sqlite"

if not os.path.exists(DB_FILE):
  raise Exception("Database file does not exist")

def db_connect():
  return connect(DB_FILE)

def fetch_dict(c):
  columns = [x[0] for x in c.description]
  for row in c.fetchall():
    yield dict(zip(columns, row))

def escape_sql(s):
  return re.sub(r'[^a-zA-Z -_0-9]+', '', s)

app = Flask(__name__)

@app.before_request
def before_request():
  g.db = db_connect()

@app.teardown_request
def teardown_request(exception):
  if g.db: g.db.close()

@app.route("/")
def index():
  return render_template("index.html")

@app.route("/q/program-maps")
def program_maps():
  semester = request.args.get("semester")
  if not semester:
    return dumps(dict(error="Missing semester parameter"))

  c = g.db.cursor()
  c.execute("""
  select distinct program, progdesc, dept, level, semester
  from Conflict
  where semester = ?
  order by program
  """, (semester,))
  result = list(fetch_dict(c))
  return dumps(dict(response=result, error=0))

@app.route("/q/program-map")
def program_map():
  semester = request.args.get("semester")
  program = request.args.get("program")
  if not (semester and program):
    return dumps(dict(error="Missing semester parameter"))
  c = g.db.cursor()
  c.execute("""
  select distinct code, coursedesc, category, catdesc
  from Conflict
  where semester=? and program=?
  order by code
  """, (semester, program))
  result = list(fetch_dict(c))
  return dumps(dict(response=result, error=0))

@app.route("/q/courses")
def courses():
  semester = request.args.get("semester")
  keyword = request.args.get("keyword")
  if not semester:
    return dumps(dict(error="Missing semester parameter"))
  c = g.db.cursor()
  if not keyword:
    c.execute("""
    select code, title
    from Course
    order by code
    """)
  else:
    c.execute("""
    select code, title
    from Course
    where (code like '%%%(keyword)s%%' or title like '%%%(keyword)s%%')
    """ % dict(keyword=escape_sql(keyword)))

  result = list(fetch_dict(c))
  return dumps(dict(response=result, error=0))

@app.route("/q/schedule")
def schedule():
  semester = request.args.get("semester")
  if request.args.get("courses[]"):
    courses = request.args.getlist("courses[]")
  else:
    courses = request.args.get("courses", "").split(",")
  if not semester:
    return dumps(dict(error="Missing semester parameter"))
  course_clause = ",".join(["'%s'" % escape_sql(c.upper()) for c in courses])
  c = g.db.cursor()
  sql = """
  select *
  from Section natural join Course left join Schedule using (sec, code, semester)
  where Section.semester=? and Section.code in (%s)
  """ % course_clause
  print "---\n", sql
  c.execute(sql, (semester,))
  return dumps(dict(response=list(fetch_dict(c)), error=0))

@app.route("/q/free")
def free():
  semester = request.args.get("semester")
  searchbar = request.args.get("searchbar", "")
  dur = int(request.args.get("duration", "30"))
  campus = request.args.get("campus")
  location = request.args.get("location")
  capacity = request.args.get("capacity")
  btime = request.args.get("btime")
  day = request.args.get("day")
  timeonly = False if request.args.get('detail') else True
  
  search = []
  if searchbar:
    # parse the search bar
    for term in re.split(r'[ ,]+', searchbar):
      try:
        (field, pattern) = term.split(":",1)
        if field.lower() in ['campus', 'location', 'room', 'capacity', 'size',
                             'dur', 'duration']:
          if field.lower() in ['campus']:
            campus = pattern
          elif field.lower() in ['location', 'room']:
            location = pattern
          elif field.lower() in ['dur', 'duration']:
            try:
              dur = int(pattern)
            except:
              pass
          elif field.lower() in ['capacity', 'size']:
            capacity = pattern
      except:
        pass
  if campus:
    if not campus.endswith("%"): campus += "%"
    search.append(('campus', 'like', campus))
  if location:
    if not location.endswith("%"): location += "%"
    search.append(('location', 'like', location))
  if capacity:
    search.append(('capacity', '>=', int(capacity)))
  if btime:
    search.append(('btime', '=', btime))
  if day:
    search.append(('day', '=', day))

  sql = sql_freerooms(dur, search, timeonly=timeonly)
  print sql
  c = g.db.cursor()
  c.execute(sql, (semester,))
  
  result = list(fetch_dict(c))
  where = dict((x[0], x[-1]) for x in search)
  where['duration'] = dur
  return dumps(dict(response=result, error=0, where=where))

import kens_views

